1 Imports System.Data.SqlClient
2 Imports System.Security.Cryptography
3 Imports System.Text
4 Imports System.IO
5 Public Class frmAdvanceEntry
6 Sub Reset()
7 txtStaffID.Text = ""
8 txtStaffName.Text = ""
9 txtAmount.Text = ""
10 dtpEntryDate.Text = Now
11 GetData()
12 btnDelete.Enabled = False
13 btnSave.Enabled = True
14 btnUpdate.Enabled = False
15 dtpEntryDate.Enabled = True
16 End Sub
17 Sub GetData()
18 Try
19 con = New SqlConnection(cs)
20 con.Open()
21 Dim sql As String = "SELECT RTRIM(Staff.St_ID),RTRIM(Staff.StaffID),RTRIM(StaffName),sum(Amount)-sum(Deduction) FROM Staff left join AdvanceEntry on Staff.St_ID=AdvanceEntry.StaffID where Staff.Status='Active' group by StaffName,Staff.StaffID,Staff.St_ID order by StaffName"
22 cmd = New SqlCommand(sql, con)
23 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
24 dgw.Rows.Clear()
25 While (rdr.Read() = True)
26 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3))
27 End While
28 con.Close()
29 Catch ex As Exception
30 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
31 End Try
32 End Sub
33 Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
34 If Len(Trim(txtStaffID.Text)) = 0 Then
35 MessageBox.Show("Please retrieve staff info", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
36 txtStaffID.Focus()
37 Exit Sub
38 End If
39 If Len(Trim(txtAmount.Text)) = 0 Then
40 MessageBox.Show("Please enter Amount", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
41 txtAmount.Focus()
42 Exit Sub
43 End If
44
45 Try
46 auto()
47 con = New SqlConnection(cs)
48 con.Open()
49 Dim cb As String = "insert into advanceentry(ID,workingdate,StaffID,amount,deduction) VALUES (" & txtID.Text & ",@d1," & txtStID.Text & "," & txtAmount.Text & ",0)"
50 cmd = New SqlCommand(cb)
51 cmd.Parameters.AddWithValue("@d1", dtpEntryDate.Value)
52 cmd.Connection = con
53 cmd.ExecuteReader()
54 If con.State = ConnectionState.Open Then
55 con.Close()
56 End If
57 con.Close()
58 Dim st As String = "added the new advance entry having id '" & txtID.Text & "'"
59 LogFunc(lblUser.Text, st)
60 MessageBox.Show("Successfully saved", "Entry", MessageBoxButtons.OK, MessageBoxIcon.Information)
61 btnSave.Enabled = False
62 con.Close()
63 GetData()
64 Catch ex As Exception
65 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
66 End Try
67 End Sub
68
69 Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
70 If Len(Trim(txtStaffID.Text)) = 0 Then
71 MessageBox.Show("Please retrieve Staff info", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
72 txtStaffID.Focus()
73 Exit Sub
74 End If
75 If Len(Trim(txtAmount.Text)) = 0 Then
76 MessageBox.Show("Please enter Amount", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
77 txtAmount.Focus()
78 Exit Sub
79 End If
80
81 Try
82 con = New SqlConnection(cs)
83 con.Open()
84 Dim cb As String = "Update advanceentry set StaffID=" & txtStID.Text & ",Amount=" & txtAmount.Text & " where id=" & txtID.Text & ""
85 cmd = New SqlCommand(cb)
86 cmd.Connection = con
87 cmd.ExecuteReader()
88 If con.State = ConnectionState.Open Then
89 con.Close()
90 End If
91 con.Close()
92 Dim st As String = "updated the advance entry having id '" & txtID.Text & "'"
93 LogFunc(lblUser.Text, st)
94 MessageBox.Show("Successfully updated", "Entry", MessageBoxButtons.OK, MessageBoxIcon.Information)
95 btnUpdate.Enabled = False
96 con.Close()
97 GetData()
98 Catch ex As Exception
99 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
100 End Try
101 End Sub
102
103 Private Sub btnDelete_Click(sender As System.Object, e As System.EventArgs) Handles btnDelete.Click
104 Try
105 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
106 DeleteRecord()
107 End If
108 Catch ex As Exception
109 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
110 End Try
111 End Sub
112 Private Sub DeleteRecord()
113 Try
114 Dim RowsAffected As Integer = 0
115 con = New SqlConnection(cs)
116 con.Open()
117 Dim cq As String = "delete from AdvanceEntry where id=" & txtID.Text & ""
118 cmd = New SqlCommand(cq)
119 cmd.Connection = con
120 RowsAffected = cmd.ExecuteNonQuery()
121 If RowsAffected > 0 Then
122 Dim st As String = "deleted the advance entry having id '" & txtID.Text & "'"
123 LogFunc(lblUser.Text, st)
124 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
125 GetData()
126 Reset()
127 Else
128 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
129 Reset()
130 End If
131 If con.State = ConnectionState.Open Then
132 con.Close()
133
134 End If
135 Catch ex As Exception
136 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
137 End Try
138 End Sub
139
140 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
141 Me.Close()
142 End Sub
143
144 Private Sub btnNew_Click(sender As System.Object, e As System.EventArgs) Handles btnNew.Click
145 Reset()
146 End Sub
147
148 Private Sub frmAdvanceEntry_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
149 GetData()
150 End Sub
151
152 Private Sub dgw_MouseClick(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
153 Try
154 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
155 txtStID.Text = dr.Cells(0).Value.ToString
156 txtStaffID.Text = dr.Cells(1).Value.ToString
157 txtStaffName.Text = dr.Cells(2).Value.ToString
158 Catch ex As Exception
159 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
160 End Try
161 End Sub
162
163 Private Sub dgw_RowPostPaint(sender As Object, e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
164 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
165 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
166 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
167 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
168 End If
169 Dim b As Brush = SystemBrushes.ControlText
170 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
171
172 End Sub
173
174
175 Private Sub txtAmount_KeyPress(sender As Object, e As System.Windows.Forms.KeyPressEventArgs) Handles txtAmount.KeyPress
176 Dim keyChar = e.KeyChar
177
178 If Char.IsControl(keyChar) Then
179 'Allow all control characters.
180 ElseIf Char.IsDigit(keyChar) OrElse keyChar = "."c Then
181 Dim text = Me.txtAmount.Text
182 Dim selectionStart = Me.txtAmount.SelectionStart
183 Dim selectionLength = Me.txtAmount.SelectionLength
184
185 text = text.Substring(0, selectionStart) & keyChar & text.Substring(selectionStart + selectionLength)
186
187 If Integer.TryParse(text, New Integer) AndAlso text.Length > 16 Then
188 'Reject an Integereger that is longer than 16 digits.
189 e.Handled = True
190 ElseIf Double.TryParse(text, New Double) AndAlso text.IndexOf("."c) < text.Length - 3 Then
191 'Reject a real number with two many decimal places.
192 e.Handled = False
193 End If
194 Else
195 'Reject all other characters.
196 e.Handled = True
197 End If
198 End Sub
199 Private Sub auto()
200 Dim Num As Integer = 0
201 con = New SqlConnection(cs)
202 con.Open()
203 Dim sql As String = ("SELECT MAX(ID) FROM AdvanceEntry")
204 cmd = New SqlCommand(sql)
205 cmd.Connection = con
206 If (IsDBNull(cmd.ExecuteScalar)) Then
207 Num = 1
208 txtID.Text = Num.ToString
209 Else
210 Num = cmd.ExecuteScalar + 1
211 txtID.Text = Num.ToString
212 End If
213 cmd.Dispose()
214 con.Close()
215 con.Dispose()
216 End Sub
217 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
218 frmAdvanceEntryRecord.lblSet.Text = "Advance Entry"
219 frmAdvanceEntryRecord.Reset()
220 frmAdvanceEntryRecord.ShowDialog()
221 End Sub
222 End Class
